﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Entity;

namespace DataAccess
{
    public class LinkWebDA
    {
        SqlDataAdapter da;

        public DataSet getLinkWeb()
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectLinkWeb", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool CreateLinkWeb(LinkWeb lw)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("addLinkWeb", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@LinkWebName", SqlDbType.NVarChar);
                    command.Parameters["@LinkWebName"].Value = lw.LinkWebName.ToString();

                    command.Parameters.Add("@LinkWebSite", SqlDbType.NVarChar);
                    command.Parameters["@LinkWebSite"].Value = lw.LinkWebSite.ToString();

                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public LinkWeb getLinkWebById(int id)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("selectLinkWebById", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@LinkWebID", id));
                    SqlDataReader dr = cmd.ExecuteReader();
                    LinkWeb lw = null;
                    if (dr.Read())
                    {
                        int linkID = Int32.Parse(dr["LinkWebID"].ToString());
                        String linkName = dr["LinkWebName"].ToString();
                        String linkSite = dr["LinkWebSite"].ToString();
                        lw = new LinkWeb(linkID, linkName, linkSite);
                    }
                    connection.Close();
                    return lw;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool UpdateLinkWeb(LinkWeb lw)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("updateLinkWeb", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@LinkWebID", SqlDbType.Int);
                    command.Parameters["@LinkWebID"].Value = lw.LinkWebID.ToString();

                    command.Parameters.Add("@LinkWebName", SqlDbType.NVarChar);
                    command.Parameters["@LinkWebName"].Value = lw.LinkWebName.ToString();

                    command.Parameters.Add("@LinkWebSite", SqlDbType.NVarChar);
                    command.Parameters["@LinkWebSite"].Value = lw.LinkWebSite.ToString();

                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public bool DeleteLinkWeb(LinkWeb lw)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("removeLinkWeb", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@LinkWebID", lw.LinkWebID));
                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public DataSet PhanTrangGreenLife(int currPage, int recodperpage, int Pagesize)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("spLinkWeb_PhanTrang", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@currPage", currPage));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@recodperpage", recodperpage));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@Pagesize", Pagesize));
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }


    }
}
